<?php
if (isset($_GET['number'])) $number=$_GET['number'];
else exit;

function db_connect(){
    static $conn;
    if (is_object($conn)) return $conn;
    
    @$conn=new mysqli('192.168.11.16','asterisk','asteriskpass','asterisk');
    //@$conn=new mysqli('localhost','root','','phonebook');
    if (mysqli_connect_errno()) {
        return false;
    } else {
        return $conn;
    }
}

/**
 * функциия по извлечению данных из БД в виде 2-х мерного массива
 * @param type $query запрос к БД
 * @return boolean (2-х мерный массив), либо FALSE в случае неудачи. 
 */
function get_data_from_table($query) {
    $conn=db_connect();
    $conn->query('SET NAMES utf8');
    $result=$conn->query($query);
    if (!$result) return FALSE;
    $i=0;
    $j=0;
    if (!$result->num_rows) return FALSE;
    $data=array();
    while ($row=$result->fetch_assoc()){
        foreach ($row as $key => $value) {
            $data[$i][$j]=$value;
            $j++;
        }
        $i++;
        $j=0;
    } 
    return $data;    
}

function get_ATC_from_db($number){

    $query="SELECT
  CASE (tx.count1 > 1)
  WHEN TRUE THEN
    case (tz.org=tz.dep)
      when TRUE
         then tz.org
      when FALSE
         then concat(tz.dep,' - ',tz.org)
    end
  WHEN FALSE THEN
    substring(tz.name, 1, locate(' ', tz.name))
  END AS 'name'
FROM

  (
  SELECT t1.name AS 'name'
       , CASE substring(t3.number, 1, 2)
         WHEN '28' THEN
           concat('83852', replace(t3.number, '-', ''))
         WHEN '(3' THEN
           substring(concat('8', replace(replace(replace(replace(t3.number, ' ', ''), ')', ''), '(', ''), '-', '')), 1, 11)
         WHEN '(8' THEN
           replace(replace(replace(replace(t3.number, ' ', ''), ')', ''), '(', ''), '-', '')
         WHEN '8-' THEN
           replace(t3.number, '-', '')
         ELSE
           replace(replace(replace(replace(t3.number, ' ', ''), ')', ''), '(', ''), '-', '')
         END AS 'fnumber'
       , t4.name as 'dep'
       , t5.name AS 'org'

  FROM
    `emp` AS t1
  JOIN `tel_emp` AS t2
  ON t1.id = t2.id_emp
  JOIN `tel` AS t3
  ON t2.id_tel = t3.id
  JOIN `dep` AS t4
  ON t4.id = t1.id_dep
  JOIN `org` AS t5
  ON t5.id = t4.id_org
  ) AS tz
LEFT JOIN
(

SELECT count(t1.name) AS 'count1'
     , CASE substring(t3.number, 1, 2)
       WHEN '28' THEN
         concat('83852', replace(t3.number, '-', ''))
       WHEN '(3' THEN
         substring(concat('8', replace(replace(replace(replace(t3.number, ' ', ''), ')', ''), '(', ''), '-', '')), 1, 11)
       WHEN '(8' THEN
         replace(replace(replace(replace(t3.number, ' ', ''), ')', ''), '(', ''), '-', '')
       WHEN '8-' THEN
         replace(t3.number, '-', '')
       ELSE
         t3.number
       END AS 'fnumber1'


FROM
  `emp` AS t1
JOIN `tel_emp` AS t2
ON t1.id = t2.id_emp
JOIN `tel` AS t3
ON t2.id_tel = t3.id
JOIN `dep` AS t4
ON t4.id = t1.id_dep
GROUP BY
  (
  CASE substring(t3.number, 1, 2)
  WHEN '28' THEN
    concat('83852', replace(t3.number, '-', ''))
  WHEN '(3' THEN
    substring(concat('8', replace(replace(replace(replace(t3.number, ' ', ''), ')', ''), '(', ''), '-', '')), 1, 11)
  WHEN '(8' THEN
    replace(replace(replace(replace(t3.number, ' ', ''), ')', ''), '(', ''), '-', '')
  WHEN '8-' THEN
    replace(t3.number, '-', '')
  ELSE
    t3.number
  END)


) AS tx
ON tz.fnumber = tx.fnumber1
where tz.fnumber LIKE '%".$number."%'
  limit 1";
    $array=get_data_from_table($query);
    echo $array[0][0];
}

get_ATC_from_db($number);

?>
